﻿CREATE procedure [dbo].[spSaveSystemVariance]
(
	@year int
	, @date datetime
	, @ReferenceDate date
)
as

set ansi_nulls OFF

DECLARE @lastDatePrevious date, @yearText varchar(4), @weekNumber varchar(4), @dayOfWeek varchar(4), @dateText varchar(50), @lastDatePreviousText varchar(50)

DECLARE @query nvarchar(max)
SET @yearText = CONVERT(varchar(4), @year);
SET @dateText = CONVERT(varchar(50), @date);

set @query = N'select @lastDatePrevious = ISNULL(MAX(ReferenceDate),''' +  CONVERT(varchar(30), @date) + ''' ) from BookOfWork' 
	+ @yearText + ' WHERE ReferenceDate < ''' + @dateText + ''''

exec sp_executesql @query, N'@lastDatePrevious date output', @lastDatePrevious output;

SET @weekNumber =  CONVERT(varchar(4),datepart(day, datediff(day, 0, @date)/7 * 7)/7 + 1)
SET @dayOfWeek =  CONVERT(varchar(4), DATEPART(weekday, @date))
SET @lastDatePreviousText = CONVERT(varchar(50), @lastDatePrevious);

SET @query = 
'MERGE BookOfWorkSystemVariance AS Target
USING 
(
	SELECT DISTINCT ' + @yearText + ' as PeriodYear
	, ISNULL(currentBow.ProgramId, previousBow.ProgramId) as [ProgramId]
	, ISNULL(currentBow.ProgramName, previousBow.ProgramName) as [ProgramName]
	, ISNULL(currentBow.ProgramManager, previousBow.ProgramManager) as [ProgramManager]
	, ISNULL(currentBow.ProjectId, previousBow.ProjectId) as [ProjectId]
	, ISNULL(currentBow.ProjectName, previousBow.ProjectName) as [ProjectName]
	, ISNULL(currentFinancialType.AccountLevel1, previousFinancialType.AccountLevel1) as [AccountLevel1]
	, ISNULL(currentBow.BowFlag, previousBow.BowFlag) as [BowFlag]
	, ISNULL(currentBow.SystemGOCCode, previousBow.SystemGOCCode) as SystemGocCode
	, SystemGOC.ManagedGeography as [SystemGeographyCode]
	, CONVERT(DECIMAL(38,3), ISNULL(previousBow.ProjectAccount1SystemGOCSum, 0)) as [Date0]
	, CONVERT(DECIMAL(38,3), ISNULL(currentBow.ProjectAccount1SystemGOCSum, 0)) as [Date1]
	--, CONVERT(DECIMAL(38,3), SUM(ISNULL(previousBow.ProjectAccount1SystemGOCSum, 0))) as [Date0]
	--, CONVERT(DECIMAL(38,3), SUM(ISNULL(currentBow.ProjectAccount1SystemGOCSum, 0))) as [Date1]
	FROM BookOfWork' + @yearText + ' as currentBow
	INNER JOIN FinancialType currentFinancialType on currentBow.FinancialTypeId = currentFinancialType.Id
	FULL OUTER JOIN BookOfWork' + @yearText + ' previousBow on previousBow.InitiativeId = currentBow.InitiativeId
		AND previousBow.ProgramId = currentBow.ProgramId
		AND previousBow.ProjectId = currentBow.ProjectId
		AND previousBow.SystemGocCode = currentBow.SystemGocCode
		AND previousBow.FinancialTypeId = currentBow.FinancialTypeId
		AND previousBow.ReferenceDate = ''' + @lastDatePreviousText + '''	
	LEFT JOIN FinancialType previousFinancialType on previousFinancialType.Id = previousBow.FinancialTypeId
	INNER JOIN GOC as SystemGOC on (SystemGOC.GocAttributes = currentBow.SystemGocCode OR SystemGOC.GocAttributes = previousBow.SystemGocCode)
		AND SystemGOC.ManagedGeography = ''BR''
	Where currentBow.ReferenceDate = ''' + @dateText + ''' 
	AND (CONVERT(DECIMAL(38,0), ISNULL(currentBow.ProjectAccount1SystemGOCSum, 0) - ISNULL(previousBow.ProjectAccount1SystemGOCSum, 0)) > 1
		OR CONVERT(DECIMAL(38,0), ISNULL(currentBow.ProjectAccount1SystemGOCSum, 0) - ISNULL(previousBow.ProjectAccount1SystemGOCSum, 0)) < -1)
	AND (currentBow.ProgramName IS NOT NULL OR previousBow.ProgramName IS NOT NULL)
	--GROUP BY ISNULL(currentBow.ProgramId, previousBow.ProgramId) 
	--	, ISNULL(currentBow.ProgramName, previousBow.ProgramName)
	--	, ISNULL(currentBow.ProgramManager, previousBow.ProgramManager)
	--	, ISNULL(currentBow.ProjectId, previousBow.ProjectId)
	--	, ISNULL(currentBow.ProjectName, previousBow.ProjectName)
	--	, ISNULL(currentFinancialType.AccountLevel1, previousFinancialType.AccountLevel1) 
	--	, ISNULL(currentBow.BowFlag, previousBow.BowFlag) 
	--	, ISNULL(currentBow.SystemGOCCode, previousBow.SystemGOCCode)
	--	, SystemGOC.ManagedGeography
	--HAVING CONVERT(DECIMAL(38,0), SUM(ISNULL(currentBow.ProjectAccount1SystemGOCSum, 0)) - SUM(ISNULL(previousBow.ProjectAccount1SystemGOCSum, 0))) <> 0
) 
AS Source
ON 
(
	Target.ProgramId = Source.ProgramId
	AND Target.ProjectId = Source.ProjectId
	AND Target.SystemGocCode = Source.SystemGocCode
	AND Target.ProgramManager = Source.ProgramManager
	AND Target.ManagedGeography = Source.SystemGeographyCode
	AND Target.BowFlag = Source.BowFlag
	AND Target.AccountLevel1 = Source.AccountLevel1
	AND Target.PeriodYear = ' + @yearText + ' 
	AND Target.WeekNumber = ' + @weekNumber + '
	AND Target.ReferenceDate = ''' + CONVERT(varchar(50), @ReferenceDate) + ''' 
	AND Target.[Month] = ' + CONVERT(VARCHAR(2), Month(@date)) + '
)
WHEN MATCHED THEN
    UPDATE SET 
			Target.Value0 = CASE ' + @dayOfWeek + '  
				WHEN 2 THEN Source.Date0 
			END
			,Target.Value1 = CASE ' + @dayOfWeek + '  
				WHEN 3 THEN Source.Date0 
				WHEN 2 THEN Source.Date1 
			END
			,Target.Value2 = CASE ' + @dayOfWeek + '  
				WHEN 4 THEN Source.Date0 
				WHEN 3 THEN Source.Date1 
			END
			,Target.Value3 = CASE ' + @dayOfWeek + '  
				WHEN 5 THEN Source.Date0 
				WHEN 4 THEN Source.Date1 
			END
			,Target.Value4 = CASE ' + @dayOfWeek + '  
				WHEN 6 THEN Source.Date0 
				WHEN 5 THEN Source.Date1 
			END
			,Target.Value5 = CASE ' + @dayOfWeek + ' 
				WHEN 6 THEN Source.Date1 
			END
WHEN NOT MATCHED BY TARGET THEN
    INSERT 
    VALUES 
		(
			Source.PeriodYear
			, ' + @weekNumber + '
			, ''' + CONVERT(varchar(50), @ReferenceDate) + '''  
			, Source.ProgramId 
			, Source.ProgramName 
			, Source.ProgramManager 
			, Source.ProjectId 
			, Source.ProjectName 
			, Source.AccountLevel1 
			, Source.BowFlag 
			, Source.SystemGocCode 
			, Source.SystemGeographyCode 
			, CASE ' + @dayOfWeek + '  
				WHEN 2 THEN Source.Date0 
			END
			, CASE ' + @dayOfWeek + '  
				WHEN 3 THEN Source.Date0 
				WHEN 2 THEN Source.Date1 
			END
			, CASE ' + @dayOfWeek + '  
				WHEN 4 THEN Source.Date0 
				WHEN 3 THEN Source.Date1 
			END
			, CASE ' + @dayOfWeek + '  
				WHEN 5 THEN Source.Date0 
				WHEN 4 THEN Source.Date1 
			END
			, CASE ' + @dayOfWeek + '  
				WHEN 6 THEN Source.Date0 
				WHEN 5 THEN Source.Date1 
			END
			, CASE ' + @dayOfWeek + ' 
				WHEN 6 THEN Source.Date1 
			END
			,' + CONVERT(VARCHAR(2), Month(@date)) + '
		); '

EXEC(@query)